import com.example.demo.DemoApplication;
import com.example.demo.entity.BuildingInfo;
import com.example.demo.entity.RoomInfo;
import com.example.demo.entity.User;
import com.example.demo.mapper.RoomInfoMapper;
import com.example.demo.mapper.UserMapper;
import com.example.demo.service.BuildingInfoService;
import com.example.demo.service.RoomInfoService;
import com.example.demo.service.UserService;
import com.example.demo.service.impl.UserServiceImpl;
import com.example.demo.utils.Md5Util;
import org.apache.commons.math3.analysis.function.Max;
import org.apache.poi.ss.formula.functions.Roman;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.jupiter.api.Test;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import javax.annotation.Resource;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Random;

/**
 * @author chen huilong
 * @data 2022/10/21
 */
@SpringBootTest(classes = {DemoApplication.class, ImportData.class}, webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT)
public class ImportData {

    @Autowired
    private UserService userService;

    @Autowired
    private RoomInfoService roomInfoService;

    @Autowired
    private BuildingInfoService buildingInfoService;


    @Test
    public void importStudent() {
        try {
            //创建工作簿对象
            XSSFWorkbook xssfWorkbook = new XSSFWorkbook(new FileInputStream("D:\\study\\研究生\\互联网zqx\\project\\dormitory\\xlsx\\参加编程测试名单（总表）.xlsx"));
            //获取工作簿下sheet的个数
            int sheetNum = xssfWorkbook.getNumberOfSheets();
            System.out.println("该excel文件中总共有：" + sheetNum + "个sheet");
            //遍历工作簿中的所有数据
            for (int i = 0; i < sheetNum; i++) {
                //读取第i个工作表
                System.out.println("读取第" + (i + 1) + "个sheet");
                XSSFSheet sheet = xssfWorkbook.getSheetAt(i);
                //获取最后一行的num，即总行数。此处从0开始
                int maxRow = sheet.getLastRowNum();
                for (int row = 0; row <= maxRow; row++) {
                    //获取最后单元格num，即总单元格数 ***注意：此处从1开始计数***
                    int maxRol = sheet.getRow(row).getLastCellNum();
                    System.out.println("--------第" + row + "行的数据如下--------");
                    for (int rol = 0; rol < maxRol; rol++) {
                        System.out.print(sheet.getRow(row).getCell(rol) + "  ");
                    }
                    if (row == 0) continue;
                    ;
                    // 处理该行的数据
                    User user = new User();
                    user.setParentId(1L); // 学生班级id，目前觉得设计多余了嘿嘿
                    user.setUserNumber(Long.valueOf(sheet.getRow(row).getCell(1) + ""));
                    user.setUserName(sheet.getRow(row).getCell(2) + "");
                    user.setPassword(Md5Util.MD5("888888"));
                    user.setUserRole("学生");
                    user.setEmail(sheet.getRow(row).getCell(1) + "@qq.com");
                    user.setPhonenumber("13787654223");
                    user.setGender((int) (10 * Math.random() % 2) + "");
                    user.setStatus(true);
                    userService.register(user);
                    System.out.println();
                }
            }

        } catch (IOException e) {
            e.printStackTrace();
        }
    }


    @Test
    public void importRoom() {
        Integer[] buildingNum = {5, 8, 9, 13, 14};
        Long[] buildingId = {1578344490656112642L, 1578344490958102529L, 1578344491092320257L, 1578344491163623425L, 1578344491230732290L};
        LinkedList<RoomInfo> roomInfos = new LinkedList<>();  // 用于批量插入
        Random rand = new Random();
        for (int k = 0; k < 5; k++) {
            for (int i = 1; i <= 5; i++) {  // 楼层
                for (int j = 1; j <= 16; j++) { // 每层的房间号
                    String roomId = buildingNum[k] + "" + i;
                    roomId += (j < 10) ? "0" + j : j; // 补0
                    RoomInfo roomInfo = new RoomInfo();
                    roomInfo.setRoomId(roomId);
                    roomInfo.setBuildingId(buildingId[k]);
                    // 可用与剩余床位数随机生成
                    int MAX = 6, MIN = 4; // 这里规定了只有4到6人间
                    int bedCount = rand.nextInt(MAX - MIN + 1) + MIN;
                    roomInfo.setBedCount(bedCount);
                    MAX = bedCount;
                    MIN = 0;
                    roomInfo.setBedRemainder(rand.nextInt(MAX - MIN + 1) + MIN);
                    // 性别随机分配，没有考虑楼层的情况
                    String gender = "";
                    if (k == 1 || k == 2) {
                        gender = "0";
                    } else {
                        gender = (int) (10 * Math.random() % 2) + "";
                    }
                    roomInfo.setUserGender(gender);
                    roomInfo.setRoomType("上床下桌"); // 多余信息，随便设置了
                    roomInfo.setImage(roomId + ".jpg"); // 多余信息，随便设置了
                    roomInfos.add(roomInfo);
                }
            }
        }
        roomInfoService.saveBatch(roomInfos); // 批量插入
    }


    // 根据room_info表修改building_info表的数据
    @Test
    public void modify() {
        List<RoomInfo> roomInfos = roomInfoService.getBaseMapper().selectList(null);
        List<BuildingInfo> buildingInfos1 = buildingInfoService.getBaseMapper().selectList(null);
        // 置零
        for (BuildingInfo buildingInfo : buildingInfos1) {
            buildingInfo.setRoomCountWoman(0);
            buildingInfo.setRoomCountMan(0);
            buildingInfo.setBedRemainderWoman(0);
            buildingInfo.setBedRemainderMan(0);
            buildingInfo.setBedCountWoman(0);
            buildingInfo.setBedCountMan(0);
        }
        for (RoomInfo roomInfo : roomInfos) {
            for (BuildingInfo buildingInfo : buildingInfos1) {
                if (roomInfo.getBuildingId().equals(buildingInfo.getBuildingId())) { // 找到对应楼
                    // 判断房间用户性别
                    if ("0".equals(roomInfo.getUserGender())) {
                        buildingInfo.setRoomCountMan(buildingInfo.getRoomCountMan() + 1);
                        buildingInfo.setBedCountMan(buildingInfo.getBedCountMan() + roomInfo.getBedCount());
                        buildingInfo.setBedRemainderMan(buildingInfo.getBedRemainderMan() + roomInfo.getBedRemainder());
                    } else {
                        buildingInfo.setRoomCountWoman(buildingInfo.getRoomCountWoman() + 1);
                        buildingInfo.setBedCountWoman(buildingInfo.getBedCountWoman() + roomInfo.getBedCount());
                        buildingInfo.setBedRemainderWoman(buildingInfo.getBedRemainderWoman() + roomInfo.getBedRemainder());
                    }
                    break;
                }
            }
        }
        System.out.println(buildingInfos1);
        buildingInfoService.saveOrUpdateBatch(buildingInfos1);
    }
}
